﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Odbc;

namespace SupplyChainManagement
{
    public partial class Form1 : Form
    {
        string strConnect = "DSN=MySQL;UID=root;PWD=root;DATABASE=store";

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {      
            OdbcConnection dbMySQL = new OdbcConnection(strConnect);
            try
            {
                //open database
                dbMySQL.Open();

                OdbcCommand sqlCommand = dbMySQL.CreateCommand();
                //assign the SQL command
                sqlCommand.CommandText = "select * from fruits";

                //reader executes the select command
                OdbcDataReader sqlReader = sqlCommand.ExecuteReader();

                //clear the columns view
                dataGridView.Columns.Clear();
                DataGridViewColumn colHead;
                //apply default textbox columns style
                DataGridTextBoxColumn colStyle = new DataGridTextBoxColumn();
                //apply default cell style
                DataGridViewCell cell = new DataGridViewTextBoxCell();
                //num of column fields
                int dvgCount = sqlReader.FieldCount;
                int dvgStart = 0; //starting point
                while (dvgStart >= 0 && dvgStart < dvgCount)
                {
                    colHead = new DataGridViewColumn(); //create new column
                    colHead.Name = sqlReader.GetName(dvgStart); //get column name
                    colHead.CellTemplate = cell; //apply cell template
                    dataGridView.Columns.Add(colHead); //add column to the dgv 
                    dvgStart++;
                }

                dvgStart = 0;

                string[] holder = new string[dvgCount]; //query holder
                while (sqlReader.Read())
                {
                    while (dvgStart >= 0 && dvgStart < dvgCount)
                    {
                        holder[dvgStart] = sqlReader.GetString(dvgStart); //build row
                        if (dvgStart == (dvgCount - 1))
                        {
                            //if last item, whole row  has been read, therefore add to dgv row
                            dataGridView.Rows.Add(holder);
                        }
                        dvgStart++;
                    }
                    dvgStart = 0;
                }

                sqlReader.Close();
                sqlCommand.Dispose();

                dbMySQL.Close();
            }
            catch (OdbcException ex)
            {
                Console.WriteLine("Database error\n\n{0}", ex.ToString());
            }
            finally
            {
                if (dbMySQL != null) dbMySQL.Close();
            }
        }

        private void btnAdd_Clicked(object sender, EventArgs e)
        {
            System.Console.WriteLine(dataGridView.CurrentCell.Value);
            System.Console.WriteLine(dataGridView.CurrentRow);
            AddForm add = new AddForm(this);
            add.Show();
            this.Enabled = false;
        }

        private void btnDel_Click(object sender, EventArgs e)
        {
            OdbcConnection dbMySQL = new OdbcConnection(strConnect);
            try
            {
                dbMySQL.Open();

                OdbcCommand sqlCommand = dbMySQL.CreateCommand();

                string toDel = (string)dataGridView.CurrentCell.Value;

                sqlCommand.CommandText = "delete from fruits WHERE name='" + toDel + "'";

                sqlCommand.ExecuteNonQuery();

                sqlCommand.Dispose();

                dbMySQL.Close();
            }
            catch (OdbcException ex)
            {
                Console.WriteLine("Database error\n\n{0}", ex.ToString());
            }
            finally
            {
                if (dbMySQL != null) dbMySQL.Close();
            }
        }
    }
}
